********************************************************************************
*DYNAMIC IMPACTS OF PRICING GROUNDWATER
*Bruno, Jessoe, Hanemann in JAERE
*
*GROUNDWATER DEPTH DATA PROCESSING 
*MERGES GW DEPTH FILES INTO ONE AND CLEANS TO MERGE INTO FULL STATA DATASET
*******************************************************************************

clear all
capture log close
set more off

*SELECT OUTPUT DATE
global outputdate = "20210526"		 

*CHANGE DIRECTORY
* 1 campus, 2 home desktop, 3 laptop
*cd "\\Client\C$\Users\ebruno\Dropbox\Pajaro_AgInnovation" 
cd  "C:\Users\Ellen\Dropbox\Pajaro_AgInnovation" 
*cd "C:\Users\ebruno\Dropbox\Pajaro_AgInnovation" 


*GW DEPTH PROCESSING
local mylist 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 

foreach x of local mylist {
*INPUT FILES AND CLEAN
import excel "Data\groundwater_depth_to_wells_`x'_20190326.xls", sheet("groundwater_depth_to_wells_`x'") firstrow clear
destring, replace
drop X_ft Y_ft LAT LONG GPS_Date GPS_Tech ImportDate ImportTech OBJECTID County DWZ DWZ_2017
rename RASTERVALU gw_depth
rename Well_Num sitenum
generate year = `x'

save "Data\Pajaro_depth_`x'_20190326.dta", replace
}


clear
*GW DEPTH PROCESSING
local mylist  2018 2019 2020

foreach x of local mylist {
*INPUT FILES AND CLEAN
import excel "Data\groundwater_depth_to_wells_`x'_20210526.xls", sheet("groundwater_depth_to_wells_`x'") firstrow clear
destring, replace
drop X_ft Y_ft LAT LONG GPS_Date GPS_Tech ImportDate ImportTech OBJECTID County DWZ DWZ_2017 NEAR_FID NEAR_DIST
rename RASTERVALU gw_depth
rename Well_Num sitenum
generate year = `x'

save "Data\Pajaro_depth_`x'_$outputdate.dta", replace
}

append  using "Data\Pajaro_depth_2019_20210526.dta"
append  using "Data\Pajaro_depth_2018_20210526.dta"

append  using "Data\Pajaro_depth_2017_20190326.dta"
append  using "Data\Pajaro_depth_2016_20190326.dta"
append  using "Data\Pajaro_depth_2015_20190326.dta"
append  using "Data\Pajaro_depth_2014_20190326.dta"
append  using "Data\Pajaro_depth_2013_20190326.dta"
append  using "Data\Pajaro_depth_2012_20190326.dta"
append  using "Data\Pajaro_depth_2011_20190326.dta"
append  using "Data\Pajaro_depth_2010_20190326.dta"
append  using "Data\Pajaro_depth_2009_20190326.dta"
append  using "Data\Pajaro_depth_2008_20190326.dta"

duplicates drop sitenum year, force
drop if gw_depth <0

save "Data\Pajaro_depth_$outputdate.dta", replace
merge m:m sitenum using "Data\Pajaro_wells_20190306.dta" 
drop _merge 

bys outside year: sum gw_depth
